import os
import sys
import tkinter as tk
from tkinter import filedialog, messagebox
from copy import deepcopy
from datetime import datetime
from docx.shared import Pt
from docx.shared import RGBColor
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
import win32com.client
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import usage_logger
import user_data

TITLE_ROWS = ['SUPPLEMENTARY PROTOCOL', 'LABELING', 'ANALYTICAL', 'PERFORMANCE']
SPECIAL_ROWS = ['SUPPLEMENTARY PROTOCOL', 'LABELING', 'ANALYTICAL', 'PHYSICAL MECHANICAL AND ELECTRICAL HAZARD', 'COLORFASTNESS', 'FLAMMABILITY', 'PHYSICAL CHARACTERISTICS', 'WORKMANSHIP', 'PERFORMANCE', 'OTHERS']
EXCLUDE_ROWS = ['Total lead in surface coating', 'Actual use-functionality - additional testing', 'Lowe’s item verification', 'PFAS SUPPLEMENTAL']
DELETE_TITLE_ROWS = ['PHYSICAL MECHANICAL AND ELECTRICAL HAZARD', 'COLORFASTNESS', 'FLAMMABILITY', 'PHYSICAL CHARACTERISTICS', 'WORKMANSHIP', 'PERFORMANCE', 'OTHERS', 'Defects: aesthetic', 'California Proposition 65', 'PFAS SUPPLEMENTAL']
replacements = {
            '[Lowe\'s Requirement]': '',
            '(or submitted artwork)': '',
            'If they are unable to provide these necessary components please contact Lowe’s QA for additional direction.':'',
            r'CPSD-\w+-MTHD': 'SGS In-house method'  # 匹配以 CPSD- 开头，以 -MTHD 结尾的字符串
        }


# 从指定文件中提取紧跟在search_content之后的单元格内容
def search_content_from_file(file_path, search_content):
    doc = Document(file_path)
    if not doc.tables:
        return "No tables found in the document."
    table = doc.tables[0]
    for row in table.rows:
        cells = [cell.text.strip() for cell in row.cells]
        try:
            index = cells.index(search_content)
            return row.cells[index + 1].text.strip()
        except (ValueError, IndexError):
            continue  # 如果找不到search_content，继续下一行
    return None


# 在给定文档中替换所有出现的占位符placeholder为replacement
def replace_placeholder_in_document(file_path, placeholder, replacement):
    doc = Document(file_path)
    table = doc.tables[0]
    for row in table.rows:
        for cell in row.cells:
            row_data = [cell.text.strip() for cell in row.cells]  # 提前获取整行数据
            if placeholder in row_data:
                inline = cell.text.split(' ')
                for i in range(len(inline)):
                    if placeholder in inline[i]:
                        text = inline[i].replace(placeholder, replacement)
                        inline[i] = text
                cell.text = ' '.join(inline)

    # 处理页眉中的占位符
    for section in doc.sections:
        header = section.header
        if header is not None:
            # 处理页眉中的表格
            if header.tables:
                for table in header.tables:
                    for row in table.rows:
                        for cell in row.cells:
                            for paragraph in cell.paragraphs:
                                # 替换段落中的占位符1
                                for run in paragraph.runs:
                                    if placeholder in run.text:
                                        new_text = run.text.replace(placeholder, replacement)
                                        run.text = new_text
                                        # 设置单元格内容居中
                                        # paragraph.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
                                # 替换段落中的占位符2
                                if placeholder in paragraph.text:
                                    new_text = paragraph.text.replace(placeholder, replacement)
                                    paragraph.text = new_text
                                    # 遍历段落中的所有run并设置字体大小
                                    for run in paragraph.runs:
                                        font = run.font
                                        font.size = Pt(8)
                                    # 设置单元格内容靠左
                                    paragraph.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.LEFT
    current_date = datetime.now().strftime('%b. %d, %Y')
    current_date_upper = datetime.now().strftime('%b').upper() + ' ' + datetime.now().strftime('%d, %Y')
    # 在表格的第一个单元格中写入当前日期
    cell = table.cell(0, 7)
    cell2 = table.cell(0, 19)
    cell3 = table.cell(0, 15)
    paragraph = cell.paragraphs[0]
    paragraph2 = cell2.paragraphs[0]
    paragraph3 = cell3.paragraphs[0]
    paragraph.text = current_date
    paragraph.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.LEFT
    paragraph2.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.LEFT
    paragraph3.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.LEFT

    # 遍历所有节的页脚
    for section in doc.sections:
        for footer in section.footer.paragraphs:
            # 替换占位符
            if '{date}' in footer.text:
                footer.text = footer.text.replace('{date}', current_date_upper)
    doc.save(file_path)


# 从文件中提取指定内容之后的所有行数据
def extract_content_from_file(file_path, start_content):
    doc = Document(file_path)
    if not doc.tables:
        return "No tables found in the document."

    table = doc.tables[2]
    start_found = False
    extracted_rows = []

    for row in table.rows:
        # 使用集合来跟踪已读取的单元格内容
        unique_cells = set()
        row_data = []
        for cell in row.cells:
            cell_text = cell.text.strip()
            if cell not in unique_cells:
                row_data.append(cell_text)
                unique_cells.add(cell)

        # 只在当前行检查start_content
        if start_content in row_data and not start_found:
            start_found = True
            # continue  # 跳过包含start_content的那一行

        # 如果已经找到start_content，则开始收集行数据
        if start_found:
            if row_data[0] in SPECIAL_ROWS:
                extracted_rows.append([row_data[0]])  # 只保留第一个单元格
            elif row_data[5] != 'USA' and row_data[1] not in EXCLUDE_ROWS:
                for old, new in replacements.items():
                    row_data[4] = row_data[4].replace(old, new).strip()

                extracted_rows.append([row_data[1], row_data[2], row_data[4], '', row_data[6]])

    return extracted_rows or "No data found after specified content."


# 合并单元格到第一个单元格
def merge_rows_with_first_cell_value(file_path, output_path):
    try:
        target_doc = Document(file_path)
        for table in target_doc.tables:
            for row_idx, row in enumerate(table.rows):
                row_data = [cell.text.strip() for cell in row.cells]
                if row_data[0] in SPECIAL_ROWS:
                    # 合并前三个单元格到第一个单元格
                    table.cell(row_idx, 0).merge(table.cell(row_idx, 1)).merge(table.cell(row_idx, 2))
                    # 将剩下的两个单元格内容设置为 "xx"
                    table.cell(row_idx, 3).text = '--'
                    table.cell(row_idx, 4).text = '--'
                    # 合并该行的所有单元格到第一个单元格
                    # for col_idx in range(1, len(row.cells)):
                    #     table.cell(row_idx, 0).merge(table.cell(row_idx, 1)).merge(table.cell(row_idx, 2)).merge(
                    #         table.cell(row_idx, 3)).merge(table.cell(row_idx, 4))
        # 保存修改后的文档
        target_doc.save(output_path)
        print("Document saved successfully.")
    except Exception as e:
        print(f"Error processing the document: {e}")


def find_visually_merged_rows(path):
    doc = Document(path)
    table = doc.tables[1]  # 仅处理第二个表格
    # merged_found = False   # 标记是否已找到内容为ANALYTICAL的行
    first_modification = True  # 新增标记，用于控制首次修改行为

    for row_idx, row in enumerate(table.rows):
        if len(row.cells) == 0:
            continue  # 跳过空行
        base_content = row.cells[0].text.strip()

        # 检查该行中的前三个单元格（如果存在）是否与基准内容相同
        if len(row.cells) >= 3:
            is_visually_merged = all(cell.text.strip() == base_content for cell in row.cells[:3])

            if is_visually_merged:
                # 排除特定内容
                if base_content in ('LABELING', 'SUPPLEMENTARY PROTOCOL', 'ANALYTICAL'):
                    continue

                # # 如果找到了ANALYTICAL，则设置标记
                # if base_content == 'ANALYTICAL':
                #     merged_found = True
                #     continue

                # 如果当前行为最后一行且合并了前三格，直接删除该行
                if row_idx == len(table.rows) - 1:
                    row._element.getparent().remove(row._element)
                    continue

                # 如果下一个合并行需要被修改，并且当前行是合并的，则执行修改
                if is_visually_merged:
                    if first_modification:
                        # 第一次修改时不删除行
                        row.cells[0].text = 'PERFORMANCE'
                        first_modification = False
                    else:
                        # 非首次修改时删除行
                        row._element.getparent().remove(row._element)

    doc.save(path)


# 移动行到表格末尾
def move_rows_by_criteria(target_table, start_criteria):
    """
    移动从首格为start_criteria开始的行到首格为end_criteria结尾的行之间的所有行到表格末尾。
    :param target_table: 目标表格对象
    :param start_criteria: 起始行的第一个单元格内容
    # :param end_criteria: 结束行的第一个单元格内容
    """
    start_index = None
    end_index = None

    for index, row in enumerate(target_table.rows):
        first_cell = row.cells[0].text.strip()
        if first_cell == start_criteria:
            start_index = index
        elif start_index is not None:
            if first_cell in TITLE_ROWS:
                end_index = index
                break  # 找到结束行后退出循环

    # 如果没有找到起始行或结束行，则不执行任何操作
    if start_index is None or end_index is None:
        print("未能找到[SUPPLEMENTARY PROTOCOL]行或结束行")
        return

    # 确保起始行在结束行之前
    if start_index >= end_index:
        print("起始行应在结束行之前")
        return

    # 提取需要移动的行
    rows_to_move = target_table.rows[start_index:end_index]

    # 从原表中删除这些行
    for row in rows_to_move:
        row._element.getparent().remove(row._element)

    # 在表格末尾添加这些行
    for row in rows_to_move:
        if row.cells[0].text.strip() not in TITLE_ROWS:
            new_row = target_table.add_row()
            for idx, cell in enumerate(row.cells):
                new_cell = new_row.cells[idx]
                new_cell.text = cell.text
        else:
            new_row = target_table.add_row()
            new_row.cells[0].text = row.cells[0].text


# 将提取的数据插入到目标文件中
def insert_data_into_table(target_file_path, extracted_data, output_path):
    target_doc = Document(target_file_path)

    if not target_doc.tables:
        return "No tables found in the target document."

    target_table = target_doc.tables[1]
    for data_row in extracted_data:
        new_row = target_table.add_row().cells
        for i, cell_data in enumerate(data_row):
            new_row[i].text = str(cell_data)

    move_rows_by_criteria(target_table, 'SUPPLEMENTARY PROTOCOL')
    try:
        target_doc.save(output_path)
    except Exception as e:
        print(f"Failed to save file: {e}")


def make_text_bold(cell):
    """使单元格中的所有文本加粗"""
    for paragraph in cell.paragraphs:
        for run in paragraph.runs:
            run.bold = True


# 添加编号
def add_numbering_to_rows(doc_path):
    """
    遍历指定表格中的每一行，如果是整行合并且内容在TITLE_ROWS中，
    则在内容首部按照遍历的顺序添加编号。同时，对于合并行之后的非合并行，
    在其第一个单元格内容前添加子编号。
    """
    doc = Document(doc_path)
    table = doc.tables[1]  # 假设是第二个表格

    main_counter = 1  # 主编号计数器
    sub_counter = 1  # 子编号计数器
    is_in_section = False  # 标记是否在某个标题段落内

    for row in table.rows:
        # 检查该行是否是整行合并
        if len(row.cells) > 2 and all(cell.text.strip() == row.cells[0].text.strip() for cell in row.cells[1:3]):
            content = row.cells[0].text.strip()
            # 检查内容是否在TITLE_ROWS中
            if content in TITLE_ROWS:
                # 更新主编号并重置子编号
                row.cells[0].text = f"{main_counter}.{content}"
                main_counter += 1
                sub_counter = 1
                is_in_section = True
                # 加粗该行内容
                make_text_bold(row.cells[0])
        else:
            if is_in_section:
                # 添加子编号
                row.cells[0].text = f"{main_counter - 1}.{sub_counter} {row.cells[0].text.strip()}"
                sub_counter += 1

    doc.save(doc_path)


def update_cell_with_current_date(file_path):
    """
    修改第六章表第三行第二个单元格里的值为当前日期，格式为 Sep. 05, 2024。
    """
    doc = Document(file_path)
    if len(doc.tables) < 6:
        raise ValueError("文档中没有足够的表格。")

    table = doc.tables[5]  # 获取第六个表格
    if len(table.rows) < 3:
        raise ValueError("第六个表格中没有足够的行。")

    row = table.rows[2]  # 获取第三行
    if len(row.cells) < 2:
        raise ValueError("第三行中没有足够的单元格。")

    cell = row.cells[1]  # 获取第二个单元格
    current_date = datetime.now().strftime('%b. %d, %Y')  # 获取当前日期并格式化
    # 清空单元格内容
    for paragraph in cell.paragraphs:
        paragraph.clear()

    # 添加当前日期到单元格
    paragraph = cell.paragraphs[0]
    run = paragraph.add_run(current_date)
    run.font.size = Pt(8)  # 设置字号为8

    doc.save(file_path)  # 保存文档


# 高亮显示Lowe's
def highlight_lowers(doc_path):
    doc = Document(doc_path)

    # 遍历所有段落
    for paragraph in doc.paragraphs:
        highlight_text_in_paragraph(paragraph, 'Lowe\'s')

    # 遍历所有表格
    for table in doc.tables:
        for row in table.rows:
            for cell in row.cells:
                for paragraph in cell.paragraphs:
                    highlight_text_in_paragraph(paragraph, 'Lowe\'s')

    doc.save(doc_path)


def add_new_row_if_needed(output_path):
    ss = 'Use/care instructions that are clear and understandable shall be provided in language appropriate to destination countries.'
    doc = Document(output_path)
    if len(doc.tables) < 2:
        raise ValueError("文档中没有足够的表格。")

    table = doc.tables[1]  # 获取第二个表格
    found_xxx = False

    # 遍历表二的每一行
    for row in table.rows:
        if len(row.cells) > 2 and row.cells[2].text.strip() == ss:
            found_xxx = True
            break

    if not found_xxx:
        # 再次遍历表二的每一行，查找第一列为 'yyy' 的行
        for idx, row in enumerate(table.rows):
            if len(row.cells) > 0 and row.cells[0].text.strip() == 'ANALYTICAL':
                # 在该行上方新增一行
                new_row = table.add_row()
                new_row.cells[0].text = 'Use (if applicable)'
                new_row.cells[1].text = 'Visual'
                new_row.cells[2].text = ss
                new_table_row = deepcopy(new_row)
                table.rows[idx]._tr.addprevious(new_table_row._element)  # 将复制的行插入第idx行之后
                # 删除新增的行
                table._element.remove(new_row._tr)
                break

    doc.save(output_path)


# 高亮显示文本
def highlight_text_in_paragraph(paragraph, text_to_highlight):
    if text_to_highlight in paragraph.text:
        new_runs = []
        current_run = ''
        for run in paragraph.runs:
            if text_to_highlight in run.text:
                parts = run.text.split(text_to_highlight)
                current_run += parts[0]
                new_runs.append((current_run, False))
                new_runs.append((text_to_highlight, True))
                current_run = parts[1]
            else:
                current_run += run.text
        new_runs.append((current_run, False))

        paragraph.clear()
        for text, is_highlighted in new_runs:
            run = paragraph.add_run(text)
            if is_highlighted:
                run.font.color.rgb = RGBColor(255, 0, 0)  # 标红


# 处理单个文件
def process_single_file(folder_path, source_file, template_file):
    doc = Document(source_file)
    table = doc.tables[2]
    # 获取第二行第一个单元格的值
    row_content = table.rows[1].cells[0].text

    start_content = row_content
    all_contents = {
        'data': extract_content_from_file(source_file, start_content),
        'pd_value': search_content_from_file(source_file, 'Protocol Description'),
        'scope_value': search_content_from_file(source_file, 'Scope'),
    }

    # 确保输出文件夹存在
    output_folder = os.path.join(folder_path, 'output')
    os.makedirs(output_folder, exist_ok=True)

    # 简化文件名生成逻辑
    def generate_output_filename(pd_value):
        return f'RONA-XXXXX-{pd_value}-V1.0.docx'

    if isinstance(all_contents['data'], list):
        output_filename = generate_output_filename(all_contents['pd_value'])
        output_path = os.path.join(output_folder, output_filename)

        insert_data_into_table(template_file, all_contents['data'], output_path)
        merge_rows_with_first_cell_value(output_path, output_path)
        for placeholder, value in {'{pd}': all_contents['pd_value'], '{scope}': all_contents['scope_value']}.items():
            replace_placeholder_in_document(output_path, placeholder, value)
        find_visually_merged_rows(output_path)
        add_new_row_if_needed(output_path)
        add_numbering_to_rows(output_path)
        update_cell_with_current_date(output_path)
        highlight_lowers(output_path)
    else:
        print(all_contents['data'])


# 处理文件夹中所有非临时的.docx文件
def process_docx_files_in_folder(folder_path, template_file):
    """处理文件夹中所有非临时的.docx文件。"""
    for filename in os.listdir(folder_path):
        if filename.endswith('.docx') and not filename.startswith('~$'):
            source_file = os.path.join(folder_path, filename)
            try:
                process_single_file(folder_path, source_file, template_file)
                data = [
                    {
                        "user_name": f"{usage_logger.username}",
                        "user_location": f"{usage_logger.location}",
                        "rpa_id": "134",
                        "rpa_name": "Protocol_converting",
                        "rpa_version": "1.0.3",
                        "occurrence": f"{usage_logger.start_time}",
                        "info": "LWES_Protocol to RONA",
                        "extra": f"{filename}"
                    }
                ]
                user_data.send_monitoring_data(data)
            except Exception as e:
                root = tk.Tk()
                root.withdraw()  # 隐藏主窗口
                messagebox.showerror("文件操作出错", f"文件 {filename} 操作出错: {str(e)}")
                print(f"Error processing file {filename}: {str(e)}")
                continue  # 继续处理下一个文件
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口
    messagebox.showinfo("执行完毕", "执行完毕！")


# 选择文件夹
def choose_folder():
    root = tk.Tk()
    root.withdraw()
    folder_path = filedialog.askdirectory(title="请选择文件夹")
    return folder_path


# 将doc转换为docx
def convert_doc_to_docx(input_file, output_docx):
    word = win32com.client.DispatchEx("Word.Application")
    doc = None
    try:
        # 规范化路径
        input_file = os.path.normpath(input_file)
        # 检查文件是否存在
        if not os.path.exists(input_file):
            print(f"File does not exist: {input_file}")
            return
        doc = word.Documents.Open(input_file)
        doc.SaveAs(output_docx, FileFormat=16)  # 16代表.docx格式
        print(f"File converted successfully to {output_docx}")
    except Exception as e:
        print(f"Error converting file: {e}")
    finally:
        if doc is not None:  # 检查doc是否已经被初始化
            doc.Close()
        word.Quit()


# print("检查更新...")
# # update.check_for_updates()
# print("开始修改文件名...")
# file = choose_folder()
# for filename in os.listdir(file):
#     file_path = os.path.join(file, filename)
#     if filename.endswith('.doc'):
#         output_docx = os.path.join(file, filename + '.docx')
#         convert_doc_to_docx(file_path, output_docx)
# if file:
#     process_docx_files_in_folder(file, 'template.docx')
    
def main():
    # 获取传入的参数
    if len(sys.argv) > 1:
        template_path = sys.argv[1]  # 第一个参数是模板文件路径
        if len(sys.argv) > 2:
            # 处理带文件路径的情况
            file_path = sys.argv[2]
            process_single_file(os.path.dirname(file_path), file_path, template_path)
        else:
            # 处理无文件路径的情况
            # print("检查更新...")
            # update.check_for_updates()
            # print("开始修改文件名...")
            folder_path = choose_folder()
            for filename in os.listdir(folder_path):
                file_path = os.path.join(folder_path, filename)
                if filename.endswith('.doc'):
                    output_docx = os.path.join(folder_path, filename + '.docx')
                    convert_doc_to_docx(file_path, output_docx)
            if folder_path:
                process_docx_files_in_folder(folder_path, template_path)

if __name__ == "__main__":
    main()